<?php
declare (strict_types=1);

namespace app\command;

use app\model\OrderModel;
use PhpOffice\PhpSpreadsheet\Style\Color;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\input\Option;
use think\console\Output;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class ExportExcel extends Command
{
    protected function configure()
    {
        // 指令配置
        $this->setName('export_excel')
            ->setDescription('the export_excel command');
    }

    protected function execute(Input $input, Output $output)
    {
        // $result = $this->ladySingle("<=");
        // $cnt    = count($result);
        // $sum    = array_sum($result->column('pink_cnt'));
        // $this->singleTemplate($result, "粉", "女1={$cnt}单{$sum}瓶");

        // $result = $this->ladySingle(">");
        // $cnt    = count($result);
        // $sum    = array_sum($result->column('pink_cnt'));
        // $this->singleTemplate($result, "粉", "女2={$cnt}单{$sum}瓶");

        // $result = $this->manSingle("<=");
        // $cnt    = count($result);
        // $sum    = array_sum($result->column('blue_cnt'));
        // $this->singleTemplate($result, "蓝", "男1={$cnt}单{$sum}瓶");

        // $result = $this->manSingle(">");
        // $cnt    = count($result);
        // $sum    = array_sum($result->column('blue_cnt'));
        // $this->singleTemplate($result, "蓝", "男2={$cnt}单{$sum}瓶");


        // $result = $this->combineOrder();
        // $cnt    = count($result);
        // $sum    = array_sum($result->column('blue_cnt'));
        // $sum    += array_sum($result->column('pink_cnt'));
        // $this->combineTemplate($result, "组合={$cnt}单{$sum}瓶");

        // $result = $this->otherError();
        // $cnt    = count($result);
        // $sum    = array_sum($result->column('blue_cnt'));
        // $sum    += array_sum($result->column('pink_cnt'));
        // $this->errorTemplate($result, "国外={$cnt}单{$sum}瓶");

        // 保税组合
        $result = $this->combineOrderBond();
        $cnt    = count($result);
        $sum    = array_sum($result->column('blue_cnt'));
        $sum    += array_sum($result->column('pink_cnt'));
        $this->combineTemplate($result, "保税组合={$cnt}单{$sum}瓶");

        // 保税男士
        $result = $this->manBond();
        $cnt    = count($result);
        $sum    = array_sum($result->column('blue_cnt'));
        $this->singleTemplate($result, "蓝","保税男士={$cnt}单{$sum}瓶");

        // 保税女士
        $result = $this->ladyBond();
        $cnt    = count($result);
        $sum    = array_sum($result->column('pink_cnt'));
        $this->singleTemplate($result, "粉","保税女士={$cnt}单{$sum}瓶");

        // 基因细胞
        $result = $this->newGoodsData("cell");
        $cnt    = count($result);
        $sum    = array_sum($result->column('blue_cnt'));
        $this->singleTemplate($result, "蓝", "抗癌={$cnt}单{$sum}瓶");

        // akg男士
        $result = $this->akgMan();
        $cnt    = count($result);
        $sum    = array_sum($result->column('blue_cnt'));
        $this->singleTemplate($result, "蓝","男士={$cnt}单{$sum}瓶");

        // akg女士
        $result = $this->akgLady();
        $cnt    = count($result);
        $sum    = array_sum($result->column('pink_cnt'));
        $this->singleTemplate($result, "粉","女士={$cnt}单{$sum}瓶");

        // 银杏纳豆
        $result = $this->yingxin();
        $cnt    = count($result);
        $sum    = array_sum($result->column('blue_cnt'));
        $this->singleTemplate($result, "蓝","银杏纳豆={$cnt}单{$sum}瓶");

        // akg组合
        $result = $this->akgCombine();
        $cnt    = count($result);
        $sum    = array_sum($result->column('blue_cnt'));
        $sum    += array_sum($result->column('pink_cnt'));
        $this->combineTemplate($result, "组合={$cnt}单{$sum}瓶");

        // 护肤
        $result = $this->newGoodsData("hufu");
        $cnt    = count($result);
        $sum    = array_sum($result->column('pink_cnt'));
        $this->singleTemplate($result, "粉", "护肤={$cnt}单{$sum}瓶");


        // 指令输出
        $output->writeln('export_excel');
    }

    public function singleTemplate($data, $type = '蓝', $filename = '')
    {
        $styleArray = [
            'font' => [
                'color' => [
                    'argb' => Color::COLOR_RED, // ARGB颜色代码
                ],
            ],
        ];

        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        // 设置表头
        $sheet->setCellValue('A1', "ReferenceNumber\n（参考号）");
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getStyle('A1')->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);

        $sheet->setCellValue('B1', "CourierNumber\n（海外快递公司单号）");
        $sheet->setCellValue('C1', "TrackingNumber\n（申通单号）");
        $sheet->setCellValue('D1', "Remark\n（备注）");
        $sheet->setCellValue('E1', "Weight\n（包裹重量KG）");
        $sheet->setCellValue('F1', "ConsigneeName\n（收件人姓名）");
        $sheet->getStyle('F1')->applyFromArray($styleArray);

        $sheet->setCellValue('G1', "Idcard\n(收件人身份证号码)");
        $sheet->setCellValue('H1', "ConsigneePhone\n（收件人联系方式）");
        $sheet->getStyle('H1')->applyFromArray($styleArray);
        $sheet->getStyle('H1')->getNumberFormat()->setFormatCode('@');

        $sheet->setCellValue('I1', "ConsigneeCountry\n（收件人国家）");
        $sheet->getStyle('I1')->applyFromArray($styleArray);

        $sheet->setCellValue('J1', "ConsigneeProvince\n（收件人省）");
        $sheet->setCellValue('K1', "ConsigneeCity\n（收件人市）");
        $sheet->setCellValue('L1', "ConsigneeDistrict\n（收件人区县）");
        $sheet->setCellValue('M1', "ConsigneeAddress\n（收件人地址）");
        $sheet->getStyle('M1')->applyFromArray($styleArray);

        $sheet->setCellValue('N1', "ShipperName\n(发件人姓名)");
        $sheet->getStyle('N1')->applyFromArray($styleArray);

        $sheet->setCellValue('O1', "ShipperCountry\n(发件人国家)");
        $sheet->getStyle('O1')->applyFromArray($styleArray);

        $sheet->setCellValue('P1', "ShipperProvince\n(发件人省/州)");
        $sheet->setCellValue('Q1', "ShipperCity\n(发件人市)");
        $sheet->setCellValue('R1', "ShipperDistrict\n（发件人区）");
        $sheet->setCellValue('S1', "ShipperAddress\n（发件人地址）");
        $sheet->getStyle('S1')->applyFromArray($styleArray);

        $sheet->setCellValue('T1', "ShipperPhone\n（发件人联系方式）");
        $sheet->getStyle('T1')->applyFromArray($styleArray);

        $sheet->setCellValue('U1', "Sku\n（商品编码）");
        $sheet->setCellValue('V1', "Name\n（商品名称）");
        $sheet->getStyle('V1')->applyFromArray($styleArray);

        $sheet->setCellValue('W1', "Qty\n（数量）");
        $sheet->getStyle('W1')->applyFromArray($styleArray);

        $sheet->setCellValue('X1', "Price\n（申报价值）");
        $sheet->getStyle('X1')->applyFromArray($styleArray);

        $sheet->setCellValue('Y1', "Category\n(品类)");
        $sheet->setCellValue('Z1', "Brand\n(品牌)");
        $sheet->setCellValue('AA1', "Unit\n(商品单位)");
        $sheet->setCellValue('AB1', "GoodsRemark\n(商品备注)");

        // 从第二行开始写入数据
        $row = 2;
        foreach ($data as $item) {
            $sheet->getStyle('A' . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
            $cellValue = $item['order_sn'];
            $sheet->setCellValueExplicit('A' . $row, $cellValue, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
//            $sheet->setCellValue('A'.$row, $cellValue);
//            $sheet->setCellValue('B'.$row, $item[1]);
//            $sheet->setCellValue('C'.$row, $item[2]);
//            $sheet->setCellValue('D'.$row, $item[2]);
//            $sheet->setCellValue('E'.$row, $item[2]);
            $sheet->setCellValue('F' . $row, $item['customer']);
//            $idcard = trim($item['idcard']);
//            if (strlen($idcard) != 18) {
//                $sheet->getStyle('G'.$row)->applyFromArray($styleArray);
//            }
            $idcard = $item['old_address'];
            $sheet->setCellValueExplicit('G' . $row, $idcard, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
            $sheet->getStyle('H' . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
            $mobile = trim($item['mobile']);
            if (strlen($mobile) != 11) {
                $sheet->getStyle('H' . $row)->applyFromArray($styleArray);
            }
            $sheet->setCellValue('H' . $row, $mobile);
            $sheet->setCellValue('I' . $row, 'CN');
//            $sheet->setCellValue('J'.$row, $item[2]);
//            $sheet->setCellValue('K'.$row, $item[2]);
//            $sheet->setCellValue('L'.$row, $item[2]);
            $sheet->setCellValue('M' . $row, $item['address']);
            $sheet->setCellValue('N' . $row, '香港仓');
            $sheet->setCellValue('O' . $row, 'CN');
//            $sheet->setCellValue('P'.$row, $item[2]);
//            $sheet->setCellValue('Q'.$row, $item[2]);
//            $sheet->setCellValue('R'.$row, $item[2]);
            $sheet->setCellValue('S' . $row, '香港特别行政区新界荃湾区中港物流仓');
            $sheet->setCellValue('T' . $row, '670***38');
//            $sheet->setCellValue('U'.$row, $item[2]);
            $sheet->setCellValue('V' . $row, $type);
            if ($type == '蓝') {
                $col = 'blue_cnt';
            } else {
                $col = 'pink_cnt';
            }
            $sheet->setCellValue('W' . $row, $item[$col]);
            $sheet->setCellValue('X' . $row, 100);
            $idcard = trim($item['idcard']);
            if (strlen($idcard) != 18) {
                $sheet->getStyle('Y' . $row)->applyFromArray($styleArray);
            }
            $sheet->setCellValueExplicit('Y' . $row, $idcard, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
            var_dump($idcard);

//            $sheet->setCellValue('Z'.$row, $item[2]);
//            $sheet->setCellValue('AA'.$row, $item[2]);
//            $sheet->setCellValue('AB'.$row, $item[2]);
            $row++;
        }
        // 创建一个 Xlsx 写入器对象
        $writer = new Xlsx($spreadsheet);
        // 保存文件到指定路径
        $path = root_path() . '/public/' . $filename . '.xlsx';
        $writer->save($path);
    }

    public function combineTemplate($data, $filename)
    {
        $data     = $data->toArray();
        $new_data = [];
        for ($i = 0; $i <= 20; $i++) {
            $new_data[$i] = array_filter($data, function ($value) use ($i) {
                return $i == $value['combine_cnt'];
            });
        }
//        $sum = 0;
//        foreach ($new_data as $item) {
//            $sum += count($item);
//        }
//        var_dump($sum);
//        return ;
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();

        foreach ($new_data as $key => $combine_data) {
            $spreadsheet->createSheet();//创建sheet
            $spreadsheet->setActiveSheetIndex($key);
            $sheet = $spreadsheet->getActiveSheet($key)->setTitle("组合" . $key);

            $styleArray = [
                'font' => [
                    'color' => [
                        'argb' => Color::COLOR_RED, // ARGB颜色代码
                    ],
                ],
            ];
            // 创建一个新的 Spreadsheet 对象
//            $spreadsheet = new Spreadsheet();
//            $sheet = $spreadsheet->getActiveSheet();
            // 设置表头
            $sheet->setCellValue('A1', "ReferenceNumber\n（参考号）");
            $sheet->getStyle('A1')->applyFromArray($styleArray);
            $sheet->getStyle('A1')->getNumberFormat()->setFormatCode('@');

            $sheet->setCellValue('B1', "CourierNumber\n（海外快递公司单号）");
            $sheet->setCellValue('C1', "TrackingNumber\n（申通单号）");
            $sheet->setCellValue('D1', "Remark\n（备注）");
            $sheet->setCellValue('E1', "Weight\n（包裹重量KG）");
            $sheet->setCellValue('F1', "ConsigneeName\n（收件人姓名）");
            $sheet->getStyle('F1')->applyFromArray($styleArray);

            $sheet->setCellValue('G1', "Idcard\n(收件人身份证号码)");
            $sheet->setCellValue('H1', "ConsigneePhone\n（收件人联系方式）");
            $sheet->getStyle('H1')->applyFromArray($styleArray);
            $sheet->getStyle('H1')->getNumberFormat()->setFormatCode('@');

            $sheet->setCellValue('I1', "ConsigneeCountry\n（收件人国家）");
            $sheet->getStyle('I1')->applyFromArray($styleArray);

            $sheet->setCellValue('J1', "ConsigneeProvince\n（收件人省）");
            $sheet->setCellValue('K1', "ConsigneeCity\n（收件人市）");
            $sheet->setCellValue('L1', "ConsigneeDistrict\n（收件人区县）");
            $sheet->setCellValue('M1', "ConsigneeAddress\n（收件人地址）");
            $sheet->getStyle('M1')->applyFromArray($styleArray);

            $sheet->setCellValue('N1', "ShipperName\n(发件人姓名)");
            $sheet->getStyle('N1')->applyFromArray($styleArray);

            $sheet->setCellValue('O1', "ShipperCountry\n(发件人国家)");
            $sheet->getStyle('O1')->applyFromArray($styleArray);

            $sheet->setCellValue('P1', "ShipperProvince\n(发件人省/州)");
            $sheet->setCellValue('Q1', "ShipperCity\n(发件人市)");
            $sheet->setCellValue('R1', "ShipperDistrict\n（发件人区）");
            $sheet->setCellValue('S1', "ShipperAddress\n（发件人地址）");
            $sheet->getStyle('S1')->applyFromArray($styleArray);

            $sheet->setCellValue('T1', "ShipperPhone\n（发件人联系方式）");
            $sheet->getStyle('T1')->applyFromArray($styleArray);

            $sheet->setCellValue('U1', "Sku\n（商品编码）");
            $sheet->setCellValue('V1', "Name\n（商品名称）");
            $sheet->getStyle('V1')->applyFromArray($styleArray);

            $sheet->setCellValue('W1', "Qty\n（数量）");
            $sheet->getStyle('W1')->applyFromArray($styleArray);

            $sheet->setCellValue('X1', "Price\n（申报价值）");
            $sheet->getStyle('X1')->applyFromArray($styleArray);

            $sheet->setCellValue('Y1', "Category\n(品类)");
            $sheet->setCellValue('Z1', "Brand\n(品牌)");
            $sheet->setCellValue('AA1', "Unit\n(商品单位)");
            $sheet->setCellValue('AB1', "GoodsRemark\n(商品备注)");
            $sheet->setCellValue('AC1', "辅助");
            $sheet->getStyle('AC1')->applyFromArray($styleArray);

            $sheet->setCellValue("AD1", "身份证号");

            $row = 2;
            foreach ($combine_data as $item) {
                $sheet->getStyle('A' . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
                $cellValue = $item['order_sn'];
                $sheet->setCellValueExplicit('A' . $row, $cellValue, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
//            $sheet->setCellValue('A'.$row, $cellValue);
//            $sheet->setCellValue('B'.$row, $item[1]);
//            $sheet->setCellValue('C'.$row, $item[2]);
//            $sheet->setCellValue('D'.$row, $item[2]);
//            $sheet->setCellValue('E'.$row, $item[2]);
                $sheet->setCellValue('F' . $row, $item['customer']);
                // $idcard = trim($item['idcard']);
                // if (strlen($idcard) != 18) {
                //     $sheet->getStyle('G'.$row)->applyFromArray($styleArray);
                // }
                $idcard = $item['old_address'];
                $sheet->setCellValueExplicit('G' . $row, $idcard, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $sheet->getStyle('H' . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
                $mobile = trim($item['mobile']);
                if (strlen($mobile) != 11) {
                    $sheet->getStyle('H' . $row)->applyFromArray($styleArray);
                }
                $sheet->setCellValue('H' . $row, (string)$mobile);
                $sheet->setCellValue('I' . $row, 'CN');
//            $sheet->setCellValue('J'.$row, $item[2]);
//            $sheet->setCellValue('K'.$row, $item[2]);
//            $sheet->setCellValue('L'.$row, $item[2]);
                $sheet->setCellValue('M' . $row, $item['address']);
                $sheet->setCellValue('N' . $row, '香港仓');
                $sheet->setCellValue('O' . $row, 'CN');
//            $sheet->setCellValue('P'.$row, $item[2]);
//            $sheet->setCellValue('Q'.$row, $item[2]);
//            $sheet->setCellValue('R'.$row, $item[2]);
                $sheet->setCellValue('S' . $row, '香港特别行政区新界荃湾区中港物流仓');
                $sheet->setCellValue('T' . $row, '670***38');
//            $sheet->setCellValue('U'.$row, $item[2]);
                $sheet->setCellValue('V' . $row, "粉");
                $sheet->setCellValue('W' . $row, $item['pink_cnt']);
                $sheet->setCellValue('X' . $row, 100);
//            $sheet->setCellValue('Y'.$row, $item[2]);
//            $sheet->setCellValue('Z'.$row, $item[2]);
//            $sheet->setCellValue('AA'.$row, $item[2]);
//            $sheet->setCellValue('AB'.$row, $item[2]);
                $sheet->setCellValue('AC' . $row, '组合' . $item['combine_cnt']);

                $sheet->setCellValue('V' . ($row + 1), "蓝");
                $sheet->setCellValue('W' . ($row + 1), $item['blue_cnt']);
                $sheet->setCellValue('X' . ($row + 1), 100);
                $sheet->setCellValue('AC' . ($row + 1), '组合' . $item['combine_cnt']);

                $idcard = trim($item['idcard']);
                if (strlen($idcard) != 18) {
                    $sheet->getStyle('AD'.$row)->applyFromArray($styleArray);
                }
                $sheet->setCellValueExplicit('AD' . $row, $idcard, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                $row += 2;
            }
        }

        // 创建一个 Xlsx 写入器对象
        $writer = new Xlsx($spreadsheet);
        // 保存文件到指定路径
        $path = root_path() . '/public/' . $filename . '.xlsx';
        $writer->save($path);
    }

    public function errorTemplate($data, $filename)
    {
        $styleArray = [
            'font' => [
                'color' => [
                    'argb' => Color::COLOR_RED, // ARGB颜色代码
                ],
            ],
        ];
        // 创建一个新的 Spreadsheet 对象
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        // 设置表头
        $sheet->setCellValue('A1', "ReferenceNumber\n（参考号）");
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getStyle('A1')->getNumberFormat()->setFormatCode('@');

        $sheet->setCellValue('B1', "CourierNumber\n（海外快递公司单号）");
        $sheet->setCellValue('C1', "TrackingNumber\n（申通单号）");
        $sheet->setCellValue('D1', "Remark\n（备注）");
        $sheet->setCellValue('E1', "Weight\n（包裹重量KG）");
        $sheet->setCellValue('F1', "ConsigneeName\n（收件人姓名）");
        $sheet->getStyle('F1')->applyFromArray($styleArray);

        $sheet->setCellValue('G1', "Idcard\n(收件人身份证号码)");
        $sheet->setCellValue('H1', "ConsigneePhone\n（收件人联系方式）");
        $sheet->getStyle('H1')->applyFromArray($styleArray);
        $sheet->getStyle('H1')->getNumberFormat()->setFormatCode('@');

        $sheet->setCellValue('I1', "ConsigneeCountry\n（收件人国家）");
        $sheet->getStyle('I1')->applyFromArray($styleArray);

        $sheet->setCellValue('J1', "ConsigneeProvince\n（收件人省）");
        $sheet->setCellValue('K1', "ConsigneeCity\n（收件人市）");
        $sheet->setCellValue('L1', "ConsigneeDistrict\n（收件人区县）");
        $sheet->setCellValue('M1', "ConsigneeAddress\n（收件人地址）");
        $sheet->getStyle('M1')->applyFromArray($styleArray);

        $sheet->setCellValue('N1', "ShipperName\n(发件人姓名)");
        $sheet->getStyle('N1')->applyFromArray($styleArray);

        $sheet->setCellValue('O1', "ShipperCountry\n(发件人国家)");
        $sheet->getStyle('O1')->applyFromArray($styleArray);

        $sheet->setCellValue('P1', "ShipperProvince\n(发件人省/州)");
        $sheet->setCellValue('Q1', "ShipperCity\n(发件人市)");
        $sheet->setCellValue('R1', "ShipperDistrict\n（发件人区）");
        $sheet->setCellValue('S1', "ShipperAddress\n（发件人地址）");
        $sheet->getStyle('S1')->applyFromArray($styleArray);

        $sheet->setCellValue('T1', "ShipperPhone\n（发件人联系方式）");
        $sheet->getStyle('T1')->applyFromArray($styleArray);

        $sheet->setCellValue('U1', "Sku\n（商品编码）");
        $sheet->setCellValue('V1', "Name\n（商品名称）");
        $sheet->getStyle('V1')->applyFromArray($styleArray);

        $sheet->setCellValue('W1', "Qty\n（数量）");
        $sheet->getStyle('W1')->applyFromArray($styleArray);

        $sheet->setCellValue('X1', "Price\n（申报价值）");
        $sheet->getStyle('X1')->applyFromArray($styleArray);

        $sheet->setCellValue('Y1', "Category\n(品类)");
        $sheet->setCellValue('Z1', "Brand\n(品牌)");
        $sheet->setCellValue('AA1', "Unit\n(商品单位)");
        $sheet->setCellValue('AB1', "GoodsRemark\n(商品备注)");
        $sheet->setCellValue('AC1', "辅助");
        $sheet->getStyle('AC1')->applyFromArray($styleArray);
        $sheet->setCellValue('AD1', "身份证号");

        $row = 2;
        foreach ($data as $item) {
            $sheet->getStyle('A' . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
            $cellValue = $item['order_sn'];
            $sheet->setCellValueExplicit('A' . $row, $cellValue, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
//            $sheet->setCellValue('A'.$row, $cellValue);
//            $sheet->setCellValue('B'.$row, $item[1]);
//            $sheet->setCellValue('C'.$row, $item[2]);
//            $sheet->setCellValue('D'.$row, $item[2]);
//            $sheet->setCellValue('E'.$row, $item[2]);
            $sheet->setCellValue('F' . $row, $item['customer']);
//            $idcard = trim($item['idcard']);
//            if (strlen($idcard) != 18) {
//                $sheet->getStyle('G'.$row)->applyFromArray($styleArray);
//            }
            $idcard = $item['old_address'];
            $sheet->setCellValueExplicit('G' . $row, $idcard, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
            $sheet->getStyle('H' . $row)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);
            $mobile = trim($item['mobile']);
            if (strlen($mobile) != 11) {
                $sheet->getStyle('H' . $row)->applyFromArray($styleArray);
            }
            $sheet->setCellValue('H' . $row, (string)$mobile);
            $sheet->setCellValue('I' . $row, 'CN');
//            $sheet->setCellValue('J'.$row, $item[2]);
//            $sheet->setCellValue('K'.$row, $item[2]);
//            $sheet->setCellValue('L'.$row, $item[2]);
            $sheet->setCellValue('M' . $row, $item['address']);
            $sheet->setCellValue('N' . $row, '香港仓');
            $sheet->setCellValue('O' . $row, 'CN');
//            $sheet->setCellValue('P'.$row, $item[2]);
//            $sheet->setCellValue('Q'.$row, $item[2]);
//            $sheet->setCellValue('R'.$row, $item[2]);
            $sheet->setCellValue('S' . $row, '香港特别行政区新界荃湾区中港物流仓');
            $sheet->setCellValue('T' . $row, '670***38');
//            $sheet->setCellValue('U'.$row, $item[2]);

//            $sheet->setCellValue('Y'.$row, $item[2]);
//            $sheet->setCellValue('Z'.$row, $item[2]);
//            $sheet->setCellValue('AA'.$row, $item[2]);
//            $sheet->setCellValue('AB'.$row, $item[2]);
            $sheet->setCellValue('AC' . $row, '组合' . $item['combine_cnt']);

            if ($item['is_combine'] == 1) {
                $sheet->setCellValue('V' . $row, "粉");
                $sheet->setCellValue('W' . $row, $item['pink_cnt']);
                $sheet->setCellValue('X' . $row, 100);
                $sheet->setCellValue('V' . ($row + 1), "蓝");
                $sheet->setCellValue('W' . ($row + 1), $item['blue_cnt']);
                $sheet->setCellValue('X' . ($row + 1), 100);
                $sheet->setCellValue('AC' . ($row + 1), '组合' . $item['combine_cnt']);
                $row += 2;
            } else {
                if ($item['pink_cnt'] > 0) {
                    $sheet->setCellValue('V' . $row, "粉");
                    $sheet->setCellValue('W' . $row, $item['pink_cnt']);
                    $sheet->setCellValue('X' . $row, 100);
                }
                if ($item['blue_cnt'] > 0) {
                    $sheet->setCellValue('V' . $row, "蓝");
                    $sheet->setCellValue('W' . $row, $item['blue_cnt']);
                    $sheet->setCellValue('X' . $row, 100);
                }
                $row += 1;
            }
        }
        // 创建一个 Xlsx 写入器对象
        $writer = new Xlsx($spreadsheet);
        // 保存文件到指定路径
        $path = root_path() . '/public/' . $filename . '.xlsx';
        $writer->save($path);
    }

    /**
     * 女士
     * @return OrderModel[]|array|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function ladySingle($cond)
    {
        $where = [
            ['is_lady', '=', 1],
            ['pink_cnt', $cond, 2],
            ['is_combine', '=', 0],
            ['type', '=', 1],
            ['goods_type', '=', 'akg']
        ];
        return OrderModel::where($where)->select();
    }

    public function manSingle($cond)
    {
        $where = [
            ['is_lady', '=', 0],
            ['blue_cnt', $cond, 2],
            ['is_combine', '=', 0],
            ['type', '=', 1],
            ['goods_type', '=', 'akg']
        ];
        return OrderModel::where($where)->select();
    }

    public function combineOrder()
    {
        $where = [
            ['is_combine', '=', 1],
            ['type', '=', 1],
            ['goods_type', '=', 'akg']
        ];
        return OrderModel::where($where)->select();
    }

    /**
     * 国外的或现场取货的
     * @return OrderModel[]|array|\think\Collection
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function otherError()
    {
        $where = [
            ['type', '=', 2],
            ['goods_type', '=', 'akg']
        ];
        return OrderModel::where($where)->select();
    }

    public function newGoodsData($goods_type)
    {
        $where = [
            ['goods_type', '=', $goods_type]
        ];
        return OrderModel::where($where)->select();
    }

    // 保税组合
    public function combineOrderBond()
    {
        $where = [
            ['is_combine', '=', 1],
            ['is_bonded', '=', 1]
        ];
        return OrderModel::where($where)->select();
    }

    // 保税男士
    public function manBond()
    {
        $where = [
            ['is_lady', '=', 0],
            ['is_combine', '=', 0],
            ['is_bonded', '=', 1]
        ];
        return OrderModel::where($where)->select();
    }

    // 保税女士
    public function ladyBond()
    {
        $where = [
            ['is_lady', '=', 1],
            ['is_combine', '=', 0],
            ['is_bonded', '=', 1]
        ];
        return OrderModel::where($where)->select();
    }

    // 基因细胞
    public function geneCell()
    {
        return $this->newGoodsData('cell');
    }

    // akg男士
    public function akgMan()
    {
        $where = [
            ['is_lady', '=', 0],
            ['is_combine', '=', 0],
            // ['type', '=', 1],
            ['is_bonded', '=', 2],
            ['goods_type', '=', 'akg']
        ];
        return OrderModel::where($where)->select();
    }

    // akg女士
    public function akgLady()
    {
        $where = [
            ['is_lady', '=', 1],
            ['is_combine', '=', 0],
            // ['type', '=', 1],
            ['is_bonded', '=', 2],
            ['goods_type', '=', 'akg']
        ];
        return OrderModel::where($where)->select();
    }

    // 银杏纳豆
    public function yingxin()
    {
        return $this->newGoodsData('yxnd');
    }

    // akg组合
    public function akgCombine()
    {
        $where = [
            ['is_combine', '=', 1],
            // ['type', '=', 1],
            ['goods_type', '=', 'akg'],
            ['is_bonded', '=', 2]
        ];
        return OrderModel::where($where)->select();
    }

}
